package com.raysdata.smartcity.dao;

import com.raysdata.smartcity.Key;
import com.raysdata.smartcity.dto.BaseDTO;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * BaseDao
 * @author kzhou
 */
@Component
public class RzBaseDao {
	Log log = LogFactory.getLog(RzBaseDao.class);
	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	@Autowired
	private JdbcTemplate jdbcTemplate;



    //新增
    public <T extends BaseDTO> Long insert(String sql,T t){
        try {
            KeyHolder keyholder = new GeneratedKeyHolder();
            SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(t);
            namedParameterJdbcTemplate.update(sql, namedParameters,keyholder);
            Long result = null;
            try {
                result = keyholder.getKey().longValue();
            } catch (Exception e) {
                result = 0L;
            }
            return result;
        } catch (Exception e) {
            log.error(e.getMessage());
            return -1L;
        }
    }
	//修改
	public <T extends BaseDTO> int update(String sql,T t){
		try {
            SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(t);
            namedParameterJdbcTemplate.update(sql, namedParameters);
			return 0;
		} catch (Exception e) {
			log.error(e.getMessage());
			return -1;
		}
	}
    //删除
    public int delete(String sql,Object...ids){
        try {
            return jdbcTemplate.update(sql,ids);
        } catch (Exception e) {
            log.error(e.getMessage());
            return -1;
        }
    }

    //批量写
    public <T extends BaseDTO> void batch(final String sql,List<T> t){
        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(t.toArray());
        namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    //查询
    public <T extends BaseDTO> List<T> query(String sql, Class<T> beanClass, Object[] params) {
        return jdbcTemplate.query(sql,params,new BeanPropertyRowMapper<T>(beanClass));
    }

    //查询
    public <T extends BaseDTO> List<T> query(String sql, Class<T> beanClass) {
        return jdbcTemplate.query(sql,new BeanPropertyRowMapper<T>(beanClass));
    }

    //分页查询
    public <T extends BaseDTO> List<T> queryBySlice(String sql, Class<T> beanClass, Object[] params,int page, int count) {
//        if(page <= 0 || count <= 0){ // find all
//            return query(sql, beanClass,params);
//        }else{
//            int from = (page - 1) * count;
//            count = (count > 0) ? count : Integer.MAX_VALUE;
//            return query(sql + " LIMIT ?,?",beanClass, ArrayUtils.addAll(params, new Integer[]{from, count}));
//        }


        if(page <= 0 || count <= 0){ // find all
            return query(sql, beanClass,params);
        }else{
            int from = (page - 1) * count;
            count = (count > 0) ? count : Integer.MAX_VALUE;
            String sqlpart="";
            sqlpart="("+ sql.substring(0,sql.length())+") xx";
            String sqlcount="select count(*) total  from ";
            sqlcount=sqlcount+sqlpart;

            System.out.println("++++++++"+sqlcount);
            List<BaseDTO> totalList=query(sqlcount, BaseDTO.class, params);

            List<T> result=query(sql + " LIMIT ?,?",beanClass, ArrayUtils.addAll(params, new Object[]{from, count}));
            for(T t:result){
                t.setTotal(totalList.get(0).getTotal());
            }
            return result;
        }

    }

    //查询byID
    public <T extends BaseDTO> T queryById(String sql, Class<T> beanClass, Object... ids) {
        try {
            return jdbcTemplate.queryForObject(sql,ids,new BeanPropertyRowMapper<T>(beanClass));
        } catch (Exception e) {
            log.error(e.getMessage());
            return null;
        }
    }

    //校验值是否存在
    public int chkIsExist(String table,String col,String colVal){
        final StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) as c from ");
        sql.append(table);
        sql.append(" where ").append(col).append(" = ").append("'" + colVal + "'");
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }

    public int chkLikeIsExist(String table,String col,String colVal){
        final StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) as c from ");
        sql.append(table);
        sql.append(" where ").append(col).append(" like ").append("'%" + colVal + "%'");
        return jdbcTemplate.queryForObject(sql.toString(), Integer.class);
    }
    //可视范围查询
    public <T extends BaseDTO> List<T> queryByPermission(String sql, Class<T> beanClass, Object[] params,String permission,String dept_id,String login_name) {
        final StringBuilder query_sql = new StringBuilder();
        query_sql.append(" SELECT tt.* from ( ");
        query_sql.append(sql);
        query_sql.append(" ) tt ");
        query_sql.append(" LEFT JOIN ss_org oo ON tt.corg=oo.org_id ");

        if(permission.equals(Key.PM_ALL)){//查询全部

            return jdbcTemplate.query(sql,params,new BeanPropertyRowMapper<T>(beanClass));
        }else if(permission.equals(Key.PM_DEPT)){//查询部门

            String dept_sql="SELECT org_code FROM ss_org WHERE org_id='" + dept_id + "' ";
            List<Map<String,Object>> deptList=jdbcTemplate.queryForList(dept_sql);
            Map<String,Object> map=deptList.get(0);
            String org_code=map.get("org_code").toString();
            query_sql.append(" and oo.org_code like '%");
            query_sql.append(org_code);
            query_sql.append("%' ");

            return jdbcTemplate.query(query_sql.toString(),params,new BeanPropertyRowMapper<T>(beanClass));
        }else{//查询个人

            query_sql.append(" and oo.org_id='");
            query_sql.append(dept_id);
            query_sql.append("' where tt.cperson='");
            query_sql.append(login_name);
            query_sql.append("' ");

            return jdbcTemplate.query(query_sql.toString(),params,new BeanPropertyRowMapper<T>(beanClass));
        }

    }

    //可视范围分页查询
    public <T extends BaseDTO> List<T> queryByPermissionSlice(String sql, Class<T> beanClass, Object[] params,int page, int count,String permission,String dept_id,String login_name) {
        final StringBuilder query_sql = new StringBuilder();
        query_sql.append(" SELECT tt.* from ( ");
        query_sql.append(sql);
        query_sql.append(" ) tt ");
        query_sql.append(" LEFT JOIN ss_org oo ON tt.corg=oo.org_id ");

        if(page <= 0 || count <= 0){ // find all
            if(permission.equals(Key.PM_ALL)){//查询全部

                return query(sql, beanClass,params);
            }else if(permission.equals(Key.PM_DEPT)){//查询部门

                String dept_sql="SELECT org_code FROM ss_org WHERE org_id='" + dept_id + "' ";
                List<Map<String,Object>> deptList=jdbcTemplate.queryForList(dept_sql);
                Map<String,Object> map=deptList.get(0);
                String org_code=map.get("org_code").toString();
                query_sql.append(" where oo.org_code like '%");
                query_sql.append(org_code);
                query_sql.append("%' ");

                return query(query_sql.toString(), beanClass,params);
            }else{//查询个人

                query_sql.append(" where tt.corg='");
                query_sql.append(dept_id);
                query_sql.append("' and tt.cperson='");
                query_sql.append(login_name);
                query_sql.append("' ");

                return query(query_sql.toString(), beanClass,params);
            }
        }else{
            int from = (page - 1) * count;
            count = (count > 0) ? count : Integer.MAX_VALUE;

            if(permission.equals(Key.PM_ALL)){//查询全部

            }else if(permission.equals(Key.PM_DEPT)){//查询部门

                String dept_sql="SELECT org_code FROM ss_org WHERE org_id='" + dept_id + "' ";
                List<Map<String,Object>> deptList=jdbcTemplate.queryForList(dept_sql);
                Map<String,Object> map=deptList.get(0);
                String org_code=map.get("org_code").toString();
                query_sql.append(" where oo.org_code like '%");
                query_sql.append(org_code);
                query_sql.append("%' ");

                sql=query_sql.toString();
                System.out.print(sql);
            }else{//查询个人

                query_sql.append(" where tt.corg='");
                query_sql.append(dept_id);
                query_sql.append("' and tt.cperson='");
                query_sql.append(login_name);
                query_sql.append("' ");

                sql=query_sql.toString();
                System.out.print(sql);
            }

            String sqlpart="";
            sqlpart="("+ sql.substring(0,sql.length())+") xx";
            String sqlcount="select count(*) total  from ";
            sqlcount=sqlcount+sqlpart;

            List<BaseDTO> totalList=query(sqlcount, BaseDTO.class, params);

            List<T> result=query(sql + " LIMIT ?,?",beanClass, ArrayUtils.addAll(params, new Object[]{from, count}));
            for(T t:result){
                t.setTotal(totalList.get(0).getTotal());
            }
            return result;
        }

    }

    //根据表名获取主键字段名称
    public String getPKColNameByTableName(String tableName){
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            stat = conn.createStatement();
            rs = stat.executeQuery("select * from "+tableName);
            ResultSetMetaData rss = rs.getMetaData();
            return rss.getColumnName(1);
        } catch (SQLException e) {
            log.error(e.getMessage());
            return null;
        }finally {
            jdbcClose(conn, stat, rs);
        }
    }


    //根据表名生成insert sql
    public String makeSql4Insert(String table){
        List<String> cols = getTableColumns(table);
        final StringBuilder sql = new StringBuilder();
        sql.append("insert into ").append(table);
        sql.append("(");
        StringBuilder colStr = new StringBuilder();
        for(int i=0;i<cols.size();i++){
            colStr.append(cols.get(i)).append(",");
        }
        sql.append(colStr.substring(0,colStr.length()-1));
        sql.append(")");
        sql.append(" values( ");
        StringBuilder colStr2 = new StringBuilder();
        for(int i=0;i<cols.size();i++){
            String colVal = cols.get(i);
            if(colVal != null && colVal.equalsIgnoreCase("cdate")){//处理插入时间，直接获取系统时间
                colStr2.append("now()").append(",");
            }else{
                colStr2.append(":").append(cols.get(i)).append(",");
            }
        }
        sql.append(colStr2.substring(0,colStr2.length()-1));
        sql.append(" ) ");
        return sql.toString();
    }

    //根据表名生成update sql
    public String makeSql4Update(String table){
        List<String> cols = getTableColumns(table);
        final StringBuilder sql = new StringBuilder();
        sql.append("update ");
        sql.append(table);
        sql.append(" set ");
        StringBuilder s = new StringBuilder();
        for(int i=1;i<cols.size();i++){
            String colName = cols.get(i);
            s.append(colName).append(" = ").append(":").append(colName).append(",");
        }
        sql.append(s.substring(0,s.length()-1));
        sql.append(" where ").append(cols.get(0)).append(" = ").append(":").append(cols.get(0));
        return sql.toString();
    }

    //根据表名生成delete sql
    public String makeSql4Delete(String table){
        List<String> cols = getTableColumns(table);
        final StringBuilder sql = new StringBuilder();
        sql.append("update ");
        sql.append(table);
        sql.append(" set status='1' ");
        sql.append(" where ").append(cols.get(0)).append(" = ").append(":").append(cols.get(0));
        return sql.toString();
    }

    //根据表名生成select all sql
    public String makeSql4SelectAll(String table){
        return "select * from " + table;
    }

    //根据表名生成select by id sql
    public String makeSql4SelectById(String table){
        String pk = getPKColNameByTableName(table);
        return "select * from " + table + " where " + pk + " = ?";
    }
    private void jdbcClose(Connection conn, Statement stat, ResultSet rs) {
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat != null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //根据数据库名及表名获得所有字段
    public List<String> getTableColumns(String tableName){
        List<String> list = new ArrayList<>();
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            stat = conn.createStatement();
            rs = stat.executeQuery("select * from "+tableName);
            ResultSetMetaData rss = rs.getMetaData();
            int columnCount = rss.getColumnCount();
            for(int i = 1;i <= columnCount; i++){
                list.add(rss.getColumnName(i));
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        }finally {
            jdbcClose(conn, stat, rs);
        }
        return list;
    }

    public List<Integer> getTableColumnTypes(String tableName){
        List<Integer> list = new ArrayList<>();
        Connection conn = null;
        Statement stat = null;
        ResultSet rs = null;
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            stat = conn.createStatement();
            rs = stat.executeQuery("select * from "+tableName);
            ResultSetMetaData rss = rs.getMetaData();
            int columnCount = rss.getColumnCount();
            for(int i = 1;i <= columnCount; i++){
                list.add(rss.getColumnType(i));
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        }finally {
            jdbcClose(conn, stat, rs);
        }
        return list;
    }


}
